package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.repository.KangZhenTuRepository;
import com.css.fxfzypg.modules.baseData.entity.KangZhenTu;
import com.css.fxfzypg.modules.baseData.queryParams.KangZhenTuQueryParams;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class KangZhenTuRepositoryImpl implements KangZhenTuRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

//    @Override
//    @Transactional(rollbackFor = Exception.class)
//    public KangZhenTuVO updateKangZhenTu(KangZhenTuVO kangZhenTuVO) {
//
//        String sql = "update sys_elevation set elevation_number = :elevationNumber, elevation_band = :elevationBand, elevation_nodatavalue = :elevationNodatavalue, elevation_min = :elevationMin, elevation_max = :elevationMax  where elevation_id = '"+elevationVO.getElevationId()+"'" ;
//
//        SqlParameterSource source = new BeanPropertySqlParameterSource(elevationVO);
//        //拿到新插入的主键值（i）
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        namedParameterJdbcTemplate.update(sql,source,keyHolder);
//        return elevationVO;
//    }

    @Override
    @Transactional
    public KangZhenTu saveKangZhenTu(KangZhenTu kangZhenTu) {
        String sql = "insert into sys_kangzhentu values(:kztId,:kztNumber,:kztCounties,:kztStreet,:kztVillage,:kztShockresistance,:kztShockresistancenumber,:kztArea,:kztStatus,:kztUpdatetime,:kztCreatetime,:kztLongitude,:kztLatitude)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(kangZhenTu);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return kangZhenTu;
    }

    @Override
    public Page<KangZhenTu> getAllKangZhenTuParams(KangZhenTuQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  k.* from sys_kangzhentu k ");

        StringBuilder orderSql = new StringBuilder(" order by k.kzt_number");

        StringBuilder whereSql = new StringBuilder(" where k.kzt_status = '" + YNEnum.Y.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getKztCounties())) {
            whereSql.append(" and k.kzt_counties like '%" + queryParams.getKztCounties() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getKztStreet())) {
            whereSql.append(" and k.kzt_street like '%" + queryParams.getKztStreet() + "%'");
        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<KangZhenTu> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(KangZhenTu.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<KangZhenTu> kangZhenTus = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());

        return kangZhenTus;
    }

    @Override
    public KangZhenTu getKangZhenTuById(String id) {
        String sql = "select * from sys_kangzhentu where kzt_id = '" + id + "'";
        KangZhenTu kangZhenTu = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(KangZhenTu.class));
        return kangZhenTu;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteKangZhenTusById(List<String> ids, Timestamp timestamp) {
        String sql = "update sys_kangzhentu set kzt_status = '" + YNEnum.N + "' ,kzt_updatetime = '" + timestamp + "' " + " where kzt_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<KangZhenTu> getAll() {
        return jdbcTemplate.query("select * from sys_kangzhentu", new BeanPropertyRowMapper<>(KangZhenTu.class));
    }

    @Override
    public void importExcel(List<KangZhenTu> kangZhenTus) {
        jdbcTemplate.batchUpdate("insert into sys_kangzhentu (kzt_id,kzt_number,kzt_counties,kzt_street,kzt_village,kzt_shockresistance,kzt_shockresistancenumber,kzt_area,kzt_status,kzt_createtime,kzt_longitude,kzt_latitude)values(?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return kangZhenTus.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, kangZhenTus.get(i).getKztId());
                ps.setString(2, kangZhenTus.get(i).getKztNumber());
                ps.setString(3, kangZhenTus.get(i).getKztCounties());
                ps.setString(4, kangZhenTus.get(i).getKztStreet());
                ps.setString(5, kangZhenTus.get(i).getKztVillage());
                ps.setString(6, kangZhenTus.get(i).getKztShockresistance());
                ps.setInt(7, kangZhenTus.get(i).getKztShockresistancenumber());
                ps.setInt(8, kangZhenTus.get(i).getKztArea());
                ps.setString(9, kangZhenTus.get(i).getKztStatus());
                java.sql.Date createtime = new java.sql.Date(kangZhenTus.get(i).getKztCreatetime().getTime());
                ps.setDate(10, createtime);
                ps.setFloat(11, kangZhenTus.get(i).getKztLongitude());
                ps.setFloat(12, kangZhenTus.get(i).getKztLatitude());
            }
        });
    }

    @Override
    public int getHouseTotalByLonAndLat(String lon, String lat, Boolean flag) {
        try {
            StringBuilder sql = new StringBuilder("select sum(kzt_shockresistancenumber) sum from sys_kangzhentu where st_intersects" +
                    "((select st_transform(st_buffer(st_transform(st_geomfromtext('POINT(" + lon + " " + lat + ")',4490), 4527),20*1000),4490)), geom)");
            if (flag) {
                sql.append(" and kzt_shockresistance='疑似抗震能力严重不足'");
            }
            //String sql = "";
            int maxParentNum = jdbcTemplate.queryForObject(sql.toString(), Integer.class);

            return maxParentNum;
        } catch (Exception e) {
            return 0;
        }
    }

    @Override
    public int getByGeom(String geom, Boolean flag) {
        try {
            StringBuilder sql = new StringBuilder(" select sum(kzt_shockresistancenumber) sum from sys_kangzhentu ");

            StringBuilder whereSql = new StringBuilder(" where kzt_status = '1' and ST_Intersects(st_geometryfromtext('")
                    .append(geom).append("',4490)").append(",geom)");
            if (flag) {
                whereSql.append(" and kzt_shockresistance='疑似抗震能力严重不足'");
            }

            int maxParentNum = jdbcTemplate.queryForObject(sql.append(whereSql).toString(), Integer.class);;

            return maxParentNum;
        } catch (Exception e) {
            return 0;
        }
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_kangzhentu where province = ? and (TO_CHAR(kzt_createtime, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(kzt_updatetime, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and kzt_status = '1'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }

}
